/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbcmigration;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author tkm
 */
public class MYSQLJDBC extends JDBCDatabase {

    MYSQLJDBC(String host, String port, String dabase, String user, String pass) {
        //初始化数据
        super(host, port == null ? Constant.MYSQLPort : port, dabase == null ? "" : dabase, user, pass, false, null);
    }

    @Override
    void open() {
        try {
            Class.forName(Constant.MYSQLDriver).newInstance();    // 调用mysql驱动
            String replaceurl = Constant.MYSQLURL.replaceFirst("DATABASE_HOST", h).replaceFirst("DATABASE_PORT", p).replaceFirst("DATABASE_NAME", d);
            connection = DriverManager.getConnection(replaceurl, user, pass);//建立connection连接
        } catch (Exception e) {
            Validator.isTrue(false, "mysql  open error:" + e.getMessage());
        }
    }

    @Override
    void close() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    void MigrationTo(JDBCDatabase jDatabase) {
        try {
            Validator.isTrue(jDatabase != this, "Cannot  migration to self!");
            Validator.isTrue( !d.isEmpty(), "Database not be null!");
            open();
            jDatabase.clearDataBase();
            jDatabase.open();
            jDatabase.newDatabase(d);
            Table[] customTables = getTables(d);
            Validator.notNull(customTables, "custom ");
            for (int i = 0; i < customTables.length; i++) {
                //遍历每一个表，
                Table table = customTables[i];

                //得到每一个表的内容
                String tablecontent = "select * from  " + table.getTableName();
                Statement datecontent = connection.createStatement();
                ResultSet dataResultSet = datecontent.executeQuery(tablecontent);

                //对方数据库插入表，并插入数据
                jDatabase.insertTableContent(table, dataResultSet);
            }
        } catch (Exception ex) {
         
            Validator.isTrue(false, "mssql MigrationTo error:" + ex.getMessage());
        }
    }

    Table[] getTables(String database) {
        throw new UnsupportedOperationException("Not supported yet.");


    }

    @Override
    void newDatabase(String dabase) {
        try {
            dropDatabaseIfExist(dabase);     //在这个之前先去掉以前的数据库

            String createurl = "CREATE DATABASE DATABASE_NAME DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
            createurl = createurl.replaceFirst("DATABASE_NAME", dabase);

            //建数据库
            Statement mysqlstatement = connection.createStatement();
            mysqlstatement.execute(createurl);
        } catch (Exception ex) {
            Validator.isTrue(false, "mysql create error:" + ex.getMessage());
        }
    }

    @Override
    void dropDatabaseIfExist(String dabase) {
        try {
            //删掉数据库
            String dropurl = " DROP DATABASE   DATABASE_NAME";
            dropurl = dropurl.replaceFirst("DATABASE_NAME", dabase);
            Statement mysqlstatement = connection.createStatement();
            mysqlstatement.execute(dropurl);
        } catch (Exception ex) {
        }
    }

    @Override
    void useDatabase(String database) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    void insertTableContent(Table table, ResultSet source) {
        try {

            String tablesql = " create table  " + table.getDatabaseName() + "." + table.getTableName() + " ( ";
            Column[] columns = table.getColumns();
            String columnsql = "";
            String columnname = "";
            String columnvalue = "";
            for (int i = 0; i < columns.length; i++) {
                Column column = columns[i];
                //将对方数据库的类型转化为mysql的数据类型
                if (columnsql.isEmpty()) {
                    columnsql += column.getColumnName() + " " + columntype(column);
                } else {
                    columnsql += " , " + column.getColumnName() + " " + columntype(column);
                }

                //复制对方数据库的字段名
                if (columnname.isEmpty()) {
                    columnname += column.getColumnName();
                    columnvalue += "?";
                } else {
                    columnname += "," + column.getColumnName();
                    columnvalue += ",?";
                }

            }
            //建立表格语句完成
            Statement tableControler = connection.createStatement();
            tablesql += columnsql + ")";

            // create table
            Statement mysqlstatement = connection.createStatement();
            mysqlstatement.execute(tablesql);

            // 插入语句完成
            String sql = "INSERT  INTO   #database#   ( #name# ) VALUES ( #value#  )".replaceFirst("#database#", table.getDatabaseName() + "." + table.getTableName()).replaceFirst("#name#", columnname).replaceFirst("#value#", columnvalue);
            PreparedStatement sqlinsert = connection.prepareStatement(sql);

            // 循环对方数据，插入
            while (source.next()) {
                for (int i = 0; i < columns.length; i++) {
                    Column column = columns[i];
                    sqlinsert.setString(i + 1, source.getString(i + 1));
                }
                sqlinsert.executeUpdate();
            }

        } catch (Exception ex) {
            Validator.isTrue(false, "mysql inser table & content error:" + ex.getMessage());
        }
    }

    private String columntype(Column column) {
        String columnsqltype = "";
        //将各种字段属性改为mysql自有的字段属性
        try {

            if (Utils.teststringInArray(column.getColumnType(), "varchar,nvarchar")) {
                if (column.getLength() > 255) {
                    columnsqltype = "TEXT";
                } else {
                    columnsqltype = "VARCHAR(lenth)".replaceFirst("lenth", Integer.toString(column.getLength()));
                }
            }

            if (Utils.teststringInArray(column.getColumnType(), "ntext,text")) {
                columnsqltype = "TEXT";
            }
            if (Utils.teststringInArray(column.getColumnType(), "char,nchar")) {
                columnsqltype = "CHAR(char)".replaceFirst("char", Integer.toString(column.getLength()));
            }
            if (Utils.teststringInArray(column.getColumnType(), "int,int8,smallint,tinyint")) {
                columnsqltype = "INT";
            }
            if (Utils.teststringInArray(column.getColumnType(), "datetime,smalldatetime")) {
                columnsqltype = "DATETIME";
            }
            if (Utils.teststringInArray(column.getColumnType(), "money,smallmoney,decimal,numeric")) {

                columnsqltype = "DECIMAL(precision,scale)".replaceFirst("precision", Integer.toString(column.getLength())).replaceFirst("scale", Integer.toString(column.getDecimal()));
            }
            if (Utils.teststringInArray(column.getColumnType(), "image,binary,varbinary")) {
                columnsqltype = "BLOB";
            }
            if (Utils.teststringInArray(column.getColumnType(), "float,real")) {
                columnsqltype = "FLOAT";
            }
            if (Utils.teststringInArray(column.getColumnType(), "bigint")) {
                columnsqltype = "BIGINT";
            }
            if (Utils.teststringInArray(column.getColumnType(), "uniqueidentifier")) {
                columnsqltype = "TEXT";
            }

            if (Utils.teststringInArray(column.getColumnType(), "timestamp")) {
                columnsqltype = "TIMESTAMP";
            }
            if (Utils.teststringInArray(column.getColumnType(), "bit")) {
                columnsqltype = "BIT(bit)".replaceFirst("bit", Integer.toString(column.getLength()));
            } else {
                throw new Exception("no mathc  column  type:" + column.getColumnType());
            }
        } catch (Exception ex) {
            Validator.isTrue(false, ex.getMessage());
        }
        return columnsqltype;
    }
}
